﻿using Agile.Infra.Core.Agile;
using Agile.Infra.Core.Exceptions;
using Microsoft.AspNetCore.Http;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using System.Data;

namespace Agile.Shared.Application.Extensions;

public static class ExcelExtension
{
    private static Dictionary<string, ICellStyle> styleList = new Dictionary<string, ICellStyle>();

    public static byte[] CreateExcel(this Dictionary<string, DataTable> sheetDataList, bool addTitleRow = true, Dictionary<string, string[]>? dropDatas = null, bool isReamark = false, string? remark = null)
    {
        var workbook = new HSSFWorkbook();
        InitStyles(workbook);

        CreateDropDataSheet(workbook, dropDatas);

        foreach (var sheetData in sheetDataList)
        {
            var sheet = workbook.CreateSheet(sheetData.Key);

            if (addTitleRow)
            {
                AddTitleRow(sheet, sheetData.Key, sheetData.Value.Columns.Count - 1);
            }

            AddTableHeadRow(sheet, sheetData.Value, addTitleRow ? 1 : 0, dropDatas);

            AddContentRow(sheet, sheetData.Value);

            if (isReamark)
            {
                AddRemarkRow(sheet, sheetData.Value.Columns.Count - 1, remark);
            }
        }


        using (var ms = new MemoryStream())
        {
            workbook.Write(ms);
            var bytes = ms.ToArray();
            workbook.Close();
            return bytes;
        }
    }

    private static void AddTitleRow(ISheet sheet, string reportName, int colSpan)
    {
        //在sheet页中添加一行 
        var row = sheet.CreateRow(0);
        row.Height = 1000;

        //创建excel单元格
        var cell = row.CreateCell(0);
        //设置 单元格内容
        cell.SetCellValue(reportName);
        cell.CellStyle = styleList["title"];

        if (colSpan > 1)
        {
            var range = new CellRangeAddress(0, 0, 0, colSpan);

            sheet.AddMergedRegion(range);
        }

        //设置列宽
        for (int i = 0; i <= colSpan; i++)
        {
            sheet.SetColumnWidth(i, 15 * 256);
        }
    }
    private static void AddTableHeadRow(ISheet sheet, DataTable dt, int rowIndex, Dictionary<string, string[]>? dropDatas = null)
    {
        //在sheet页中添加一行 
        var row = sheet.CreateRow(rowIndex);

        //设置表头
        var colIndex = 0;
        foreach (DataColumn col in dt.Columns)
        {
            //创建excel单元格
            var cell = row.CreateCell(colIndex);
            //设置 单元格内容
            cell.SetCellValue(col.ToString());
            //设置 单元格样式
            cell.CellStyle = styleList["head"];

            //设置 单元格备注内容
            if (col.Caption.IsNotNullOrWhiteSpace() && !col.Caption.Equals(col.ToString(), StringComparison.OrdinalIgnoreCase))
            {
                var comment = sheet.CreateDrawingPatriarch().CreateCellComment(new HSSFClientAnchor(0, 0, 0, 0, cell.ColumnIndex, rowIndex, 3, 3));
                comment.String = new HSSFRichTextString(col.Caption);
                cell.CellComment = comment;
            }

            //设置 单元格下拉框内容
            if (dropDatas != null && dropDatas.Any() && dropDatas.ContainsKey(col.ToString()))
            {
                BindCellDropDownData(sheet, colIndex, colIndex, col.ToString());
                //SetCellDropdownList(sheet, colIndex, colIndex, dropDatas[col.ToString()]);
            }

            colIndex++;
        }
    }
    private static void AddContentRow(ISheet sheet, DataTable dt)
    {
        foreach (DataRow dr in dt.Rows)
        {
            var rowIndex = sheet.LastRowNum + 1;
            //在sheet页中添加一行 
            var row = sheet.CreateRow(rowIndex);
            var colIndex = 0;
            foreach (var col in dr.ItemArray)
            {
                //创建excel单元格
                var cell = row.CreateCell(colIndex);
                //设置 单元格内容
                cell.SetCellValue(col?.ToString());
                //设置 单元格样式  
                if (col?.GetType() == typeof(string)) cell.CellStyle = styleList["content-center"];
                else cell.CellStyle = styleList["content-right"];

                colIndex++;
            }
        }
    }
    private static void AddRemarkRow(ISheet sheet, int spanCols, string? remark)
    {
        // 获取最后一行
        int lastRowNum = sheet.LastRowNum + 1;
        IRow lastRow = sheet.CreateRow(lastRowNum);

        // 创建“备注”单元格
        ICell remarkCell = lastRow.CreateCell(0);
        remarkCell.CellStyle = styleList["content-center"];
        remarkCell.SetCellValue("备注");

        // 合并单元格
        CellRangeAddress cellRange = new CellRangeAddress(lastRowNum, lastRowNum, 1, spanCols);
        sheet.AddMergedRegion(cellRange);

        // 创建备注内容单元格
        ICell mergedCell = lastRow.CreateCell(1);

        // 应用样式到合并单元格
        for (int col = 1; col <= spanCols; col++)
        {
            ICell cell = lastRow.CreateCell(col);
            cell.CellStyle = styleList["content-center"];
        }
        mergedCell.SetCellValue(remark);
    }

    private static void InitStyles(IWorkbook workbook)
    {
        styleList = new Dictionary<string, ICellStyle>();

        styleList.Add("title", SetTitleCellStyle(workbook));
        styleList.Add("head", SetTabelHeadCellStyle(workbook));
        styleList.Add("content-center", SetContentCellStyle(workbook, HorizontalAlignment.Center));
        styleList.Add("content-right", SetContentCellStyle(workbook, HorizontalAlignment.Right));
    }
    private static ICellStyle SetTitleCellStyle(IWorkbook workbook)
    {
        var cellStyle = workbook.CreateCellStyle();

        IFont font = workbook.CreateFont();
        font.FontHeightInPoints = 20;
        font.FontName = "微软雅黑";
        font.IsBold = true;

        cellStyle.Alignment = HorizontalAlignment.Center;//水平对齐 
        cellStyle.VerticalAlignment = VerticalAlignment.Center;//垂直对齐      
        cellStyle.Indention = 0;//缩进;  
        cellStyle.SetFont(font);

        return cellStyle;
    }
    private static ICellStyle SetTabelHeadCellStyle(IWorkbook workbook)
    {
        var cellStyle = workbook.CreateCellStyle();

        IFont font = workbook.CreateFont();
        font.FontHeightInPoints = 12;
        font.FontName = "微软雅黑";
        font.IsBold = true;

        //边框  
        cellStyle.BorderBottom = BorderStyle.Thin;
        cellStyle.BorderLeft = BorderStyle.Thin;
        cellStyle.BorderRight = BorderStyle.Thin;
        cellStyle.BorderTop = BorderStyle.Thin;
        //边框颜色  
        cellStyle.BottomBorderColor = HSSFColor.OliveGreen.Black.Index;
        cellStyle.LeftBorderColor = HSSFColor.OliveGreen.Black.Index;
        cellStyle.RightBorderColor = HSSFColor.OliveGreen.Black.Index;
        cellStyle.TopBorderColor = HSSFColor.OliveGreen.Black.Index;

        cellStyle.Alignment = HorizontalAlignment.Center;//水平对齐 
        cellStyle.VerticalAlignment = VerticalAlignment.Center;//垂直对齐   
                                                               //cellStyle.WrapText = true;//自动换行   
        cellStyle.Indention = 0;//缩进;   
        cellStyle.SetFont(font);

        return cellStyle;
    }
    private static ICellStyle SetContentCellStyle(IWorkbook workbook, HorizontalAlignment Alignment)
    {
        var cellStyle = workbook.CreateCellStyle();

        IFont font = workbook.CreateFont();
        font.FontHeightInPoints = 10;
        font.FontName = "微软雅黑";
        font.IsBold = false;

        //边框  
        cellStyle.BorderBottom = BorderStyle.Thin;
        cellStyle.BorderLeft = BorderStyle.Thin;
        cellStyle.BorderRight = BorderStyle.Thin;
        cellStyle.BorderTop = BorderStyle.Thin;
        //边框颜色  
        cellStyle.BottomBorderColor = HSSFColor.OliveGreen.Black.Index;
        cellStyle.LeftBorderColor = HSSFColor.OliveGreen.Black.Index;
        cellStyle.RightBorderColor = HSSFColor.OliveGreen.Black.Index;
        cellStyle.TopBorderColor = HSSFColor.OliveGreen.Black.Index;

        cellStyle.Alignment = Alignment;//水平对齐 
        cellStyle.VerticalAlignment = VerticalAlignment.Center;//垂直对齐   
                                                               //cellStyle.WrapText = true;//自动换行   
        cellStyle.Indention = 0;//缩进;    
        cellStyle.SetFont(font);

        return cellStyle;
    }


    private static void CreateDropDataSheet(HSSFWorkbook workbook, Dictionary<string, string[]>? dropDatas)
    {
        if (dropDatas == null || dropDatas.Count == 0) return;

        foreach (var dropData in dropDatas)
        {
            var sheet = workbook.CreateSheet(dropData.Key);

            var i = 0;
            foreach (var item in dropData.Value)
            {
                var row = sheet.CreateRow(i);
                var cell = row.CreateCell(0);
                cell.SetCellValue(item);

                i++;
            }

            // 创建名称，可被其他单元格引用
            var namedCell = workbook.CreateName();
            namedCell.NameName = dropData.Key;
            // 设置名称引用的公式
            namedCell.RefersToFormula = $"{dropData.Key}!$A$1:$A${dropData.Value.Length}";
            // 隐藏Sheet
            workbook.SetSheetHidden(workbook.GetSheetIndex(sheet),SheetVisibility.VeryHidden);
        }
    }

    private static void BindCellDropDownData(ISheet sheet, int firstcol, int lastcol, string sheetName)
    {
        //加载数据,将名称为sheetName的sheet中的数据转换为List形式
        var constraint = DVConstraint.CreateFormulaListConstraint(sheetName);

        // 设置第一列的3-65534行为下拉列表
        // (3, 65534, 0, 0) ====> (起始行,结束行,起始列,结束列)
        var regions = new CellRangeAddressList(1, 65534, firstcol, lastcol);
        // 将设置下拉选的位置和数据的对应关系 绑定到一起
        var dataValidation = new HSSFDataValidation(regions, constraint);
        dataValidation.CreateErrorBox("输入不合法", "请输入或选择下拉列表中的值。");
        dataValidation.ShowPromptBox = true;

        //将数据赋给下拉列表
        sheet.AddValidationData(dataValidation);
    }
    public static void SetCellDropdownList(ISheet sheet, int firstcol, int lastcol, string[] vals)
    {
        //设置生成下拉框的行和列
        var cellRegions = new CellRangeAddressList(1, 65535, firstcol, lastcol);

        //设置 下拉框内容
        DVConstraint constraint = DVConstraint.CreateExplicitListConstraint(vals);

        //绑定下拉框和作用区域，并设置错误提示信息
        HSSFDataValidation dataValidate = new HSSFDataValidation(cellRegions, constraint);
        dataValidate.CreateErrorBox("输入不合法", "请输入或选择下拉列表中的值。");
        dataValidate.ShowPromptBox = true;

        sheet.AddValidationData(dataValidate);
    }


    /// <summary>
    /// 验证文件扩展名
    /// </summary>
    /// <param name="file"></param>
    public static void CheckExtension(this IFormFile file, string extend = "|.xls|.xlsx|")
    {
        var fileName = file.FileName;

        var fileExt = Path.GetExtension(fileName).ToLower();

        if (!extend.Contains(fileExt))
        {
            throw new BusinessException($"文件格式不正确，请上传以下格式的文件：{extend.TrimStart("|").TrimEnd("|")}");
        }
        if (file.Length == 0)
        {
            throw new BusinessException("文件无效");
        }
    }

    /// <summary>
    /// 验证模板列
    /// </summary>
    /// <param name="file"></param>
    public static void CheckTemplateColumn(this IFormFile file, string[] columnName, DataTable dt)
    {
        string lackColumn = string.Empty;
        foreach (var item in columnName)
        {
            if (!dt.Columns.Contains(item)) lackColumn += item + ",";
        }
        if (lackColumn.IsNotNullOrWhiteSpace())
        {
            throw new BusinessException($"模板错误，缺少[{lackColumn}]列，请重新下载模板");
        }
    }

    /// <summary>
    /// 获取导入Excel数据
    /// </summary>
    /// <param name="file"></param>
    /// <returns></returns>
    public static DataTable GetExcelData(this IFormFile file, Func<byte[], string, string>? uploadImageFunc = null, int maxCount = 1000)
    {
        file.CheckExtension();

        //读取excel数据
        var dt = NPOIHelper.ReadStreamToDataTable(file.OpenReadStream(), uploadImageFunc);

        if (dt == null || dt.Rows.Count == 0)
        {
            throw new BusinessException("没有可导入的数据");
        }

        if (dt.Rows.Count > maxCount)
        {
            throw new BusinessException($"导入数据数据不得超过{maxCount}条");
        }

        return dt;
    }

}
